package Server;

import java.awt.BorderLayout;
import java.sql.*;
import java.time.LocalDate;
import java.time.LocalTime;
import java.util.ArrayList;
import java.util.HashMap;

import javax.swing.JFrame;
import javax.swing.JPanel;
import javax.swing.JScrollPane;
import javax.swing.JTable;
import javax.swing.table.DefaultTableModel;

public class DBZugriff {

	private static String user = "haggis";
	private static String tabelle = "Spieler";
	private static String host = "db4free.net:3306";
	private static String database = "simplesystems";
	private static String pw = "simplesystems";
	private static String url = "jdbc:mysql://" + host + "/" + database;
	

	public static void executeUpdate(String befehl) throws Exception {
		executeUpdate(null, befehl);
	}

	public static void executeUpdate(String datenbank, String befehl)
			throws Exception {

		try (Connection connection = DriverManager.getConnection(url, user, pw);
				Statement s = connection.createStatement();) {
			s.executeUpdate(befehl);
		} catch (Exception ex) {
			System.out.println(ex.toString());
		}
	}

	// Insert into Spieler (Spielername, Punktestand) values (Werteliste);
	public static void einfuegen(String wertspalte1, int wertspalte2, LocalDate wertspalte3)
			throws Exception {
		try (Connection connection = DriverManager.getConnection(url, user, pw);
				Statement s = connection.createStatement();

		) {

			StringBuilder sb = new StringBuilder();
			sb.append("Insert Into ");
			sb.append(database + "." + tabelle + " ");
			sb.append("(" + DBBefehle.getSpalte1());
			sb.append(", " + DBBefehle.getSpalte2());
			sb.append(", " + DBBefehle.getSpalte3());
			sb.append(")");
			sb.append(" values ");
			sb.append("('" + wertspalte1);
			sb.append("', '" + wertspalte2);
			sb.append("', '" + wertspalte3);
			sb.append("');");
			System.out.println(sb);
			connection.setCatalog(database);
			s.executeUpdate(sb.toString());

		} catch (Exception ex) {
			System.out.println(ex.toString());
		}
	}
	
	// UPDATE Punktestand
	// Update Datenbankname.Tabellenname set{attribut=value}[WHERE `condition`];
	public static void aktualisierepunktestand(String spielername,
			int punktestand) {
		try (Connection connection = DriverManager.getConnection(url, user, pw);
				Statement s = connection.createStatement();) {
			
			StringBuilder sb = new StringBuilder();
			sb.append("UPDATE ");
			sb.append(database + "." + tabelle + " set ");
			sb.append("Punktestand = " + punktestand);
			sb.append(" WHERE ");
			sb.append("Spielername = '" + spielername + "';");

			System.out.println(sb);
			connection.setCatalog(database);
			s.executeUpdate(sb.toString());
		} catch (Exception ex) {
			System.out.println(ex.toString());
		}
	}

	// Datenbank der Spieler anzeigen "Select * from Spieler";
	public static void tabelleAusgeben() {

		try (Connection connection = DriverManager.getConnection(url, user, pw);
				Statement s = connection.createStatement();) {

			ResultSet rs = s.executeQuery("SELECT Spielername, Punktestand, Datum, @curRank := @curRank + 1 AS Rang"
					+ " FROM Spieler sp, (SELECT @curRank := 0) q ORDER BY Punktestand desc ");
			

			String i, n, p , d;
			DefaultTableModel model;
			model = new DefaultTableModel();

			JFrame frame = new JFrame("Spielertabelle");
			JPanel panel = new JPanel();
			panel.setLayout(new BorderLayout());
			JTable jtable = new JTable(model);
			JScrollPane tableContainer = new JScrollPane(jtable);
			panel.add(tableContainer, BorderLayout.CENTER);
			frame.getContentPane().add(panel);
			frame.pack();
			frame.setVisible(true);

			model.addColumn("Rang");
			model.addColumn("Spielername");
			model.addColumn("Punktestand");
			model.addColumn("Datum");

			while (rs.next()) {
				i = rs.getString("Rang");
				n = rs.getString("Spielername");
				p = rs.getString("Punktestand");
				d = rs.getString("Datum");

				model.addRow(new Object[] { i, n, p, d });
			}

		} catch (Exception ex) {
			System.out.println(ex.toString());
		}

	}

	
		     }
		
	
	
	

